package com.jackstraw.create.utils;

import com.jackstraw.create.param.DbToJavaParamTypeMap;
import com.jackstraw.create.pojo.DatabaseConnectInfo;
import com.jackstraw.create.pojo.FieldData;
import com.jackstraw.create.pojo.TableData;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 版权所有： 稻草人
 * 所在包：com.jackstraw.create.utils
 * 文件名：
 * 项目：create
 * 功能描述：
 * 修改时间：2021-04-16 15:50
 *
 * @author jackstraw
 */
@Slf4j
public class DatabaseUtil {

    /**
     * 数据库连接信息
     */
    private static DatabaseConnectInfo connectInfo;

    /**
     * 数据库连接对象
     */
    private static Connection conn = null;

    /**
     * 初始化数据库连接
     *
     * @param databaseConnectInfo 数据库连接信息
     */
    public static void initConnection(DatabaseConnectInfo databaseConnectInfo) throws Exception {
        // 验证当前连接是否需要重新获取
        if (connectInfo != null) {
            boolean compare = databaseConnectInfo.compare(connectInfo);
            if (compare && conn != null) {
                return;
            }
        }

        // 记录当前连接信息
        connectInfo = databaseConnectInfo;

        // 创建数据库连接
        createConnect(databaseConnectInfo);
    }

    /**
     * 创建数据库连接
     *
     * @param databaseConnectInfo
     * @throws Exception
     */
    private static void createConnect(DatabaseConnectInfo databaseConnectInfo) throws Exception {
        // 获取当前数据库类型
        DatabaseConnectInfo.DatabaseTypeEnum databaseType = DatabaseConnectInfo.DatabaseTypeEnum.getDatabaseTypeEnumByType(databaseConnectInfo.getDbType());
        if (databaseType == null) {
            throw new RuntimeException("当前数据库类型尚未兼容到当前工具中，敬请期待！");
        }
        Map<String, String> reMap;
        switch (databaseType) {
            case MYSQL8:
                reMap = initMysqlConnectPath();
                break;
            case SQLServer:
                reMap = initSQLServerConnectPath();
                break;
            default:
                throw new RuntimeException("当前数据库类型尚未兼容到当前工具中，敬请期待！");
        }

        // 关闭并创建新连接
        if (conn != null) {
            conn.close();
        }

        conn = DriverManager.getConnection(reMap.get("url"), reMap.get("username"), reMap.get("password"));
    }

    /**
     * 初始化mysql数据库连接路径
     */
    private static Map<String, String> initMysqlConnectPath() {
        Map<String, String> reMap = new HashMap<>(7);
        StringBuffer connectPath = new StringBuffer("");
        connectPath.append("jdbc:mysql://");
        connectPath.append(connectInfo.getUrl());
        connectPath.append(":");
        connectPath.append(connectInfo.getPort());
        connectPath.append("/");
        connectPath.append(connectInfo.getDbName());
        connectPath.append("?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true");
        reMap.put("url", connectPath.toString());
        reMap.put("username", connectInfo.getUsername());
        reMap.put("password", connectInfo.getPassword());
        return reMap;
    }

    private static Map<String, String> initSQLServerConnectPath() {
        Map<String, String> reMap = new HashMap<>(6);
        StringBuffer connectPath = new StringBuffer("");
        connectPath.append("jdbc:sqlserver://");
        connectPath.append(connectInfo.getUrl());
        connectPath.append(":");
        connectPath.append(connectInfo.getPort());
        connectPath.append(";databaseName=");
        connectPath.append(connectInfo.getDbName());
        reMap.put("url", connectPath.toString());
        reMap.put("username", connectInfo.getUsername());
        reMap.put("password", connectInfo.getPassword());
        return reMap;
    }

    /**
     * 获取数据库表列表
     *
     * @return
     */
    public static List<TableData> getTableList() throws Exception {
        if (conn == null) {
            throw new RuntimeException("请先初始化数据库连接！");
        }

        List<TableData> list = new ArrayList<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet tables = metaData.getTables(connectInfo.getDbName(), "%", "%", new String[]{"TABLE"});
        boolean isSqlServer = metaData.getDriverName().contains("Microsoft JDBC Driver");
        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");
            String remark = tables.getString("REMARKS");
            if (isSqlServer) {
                Statement statement = conn.createStatement();
                ResultSet resultSet = statement.executeQuery("select t1.name,t2.value FROM sys.sysobjects t1 left join sys.extended_properties t2 on t1.id=t2.major_id and t2.minor_id=0" +
                        " where t1.name = '" + tableName + "'");
                while (resultSet.next()) {
                    remark = resultSet.getString("value");
                }
                if (remark == null) {
                    remark = "";
                }
            }
            TableData tableData = new TableData();
            tableData.setTableName(tableName);
            tableData.setTableRemarks(remark);
            list.add(tableData);
        }
        return list;
    }

    /**
     * 获取数据库表列表
     *
     * @return
     */
    public static TableData getTableData(String tableName) throws Exception {
        if (conn == null) {
            throw new RuntimeException("请先初始化数据库连接！");
        }
        TableData tableData = new TableData();
        DatabaseMetaData metaData = conn.getMetaData();
//        ResultSet tables = metaData.getTables(connectInfo.getDbName().toUpperCase(), "%", tableName.toUpperCase(), new String[]{"TABLE"});
        ResultSet tables = metaData.getTables(connectInfo.getDbName(), "%", tableName, new String[]{"TABLE"});
        boolean isSqlServer = metaData.getDriverName().contains("Microsoft JDBC Driver");
        while (tables.next()) {
            String remark = tables.getString("REMARKS");
            if (isSqlServer) {
                Statement statement = conn.createStatement();
                ResultSet resultSet = statement.executeQuery("select t1.name,t2.value FROM sys.sysobjects t1 left join sys.extended_properties t2 on t1.id=t2.major_id and t2.minor_id=0" +
                        " where t1.name = '" + tableName + "'");
                while (resultSet.next()) {
                    remark = resultSet.getString("value");
                }
                if (remark == null) {
                    remark = "";
                }
            }
            tableData.setTableName(tableName);
            tableData.setTableRemarks(remark);
            String className = DbColumnUtil.getClassNameByTableName(tableName);
            tableData.setClassNameLower(DbColumnUtil.lowerFirstCase(className));
            tableData.setClassName(className);
            tableData.setTableAliasName(DbColumnUtil.getTableAlias(tableName));
            tableData.setFieldList(getFieldList(tableName));
        }
        return tableData;
    }

    /**
     * 根据表名获取字段信息
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public static List<FieldData> getFieldList(String tableName) throws Exception {
        List<String> primaryKeyList = getPrimaryKeyList(tableName);
        List<FieldData> list = new ArrayList<>();
        DatabaseMetaData metaData = conn.getMetaData();
//        ResultSet columns = metaData.getColumns(connectInfo.getDbName().toUpperCase(), null, tableName.toUpperCase(), null);
        ResultSet columns = metaData.getColumns(connectInfo.getDbName(), null, tableName, null);
        boolean isSqlServer = metaData.getDriverName().contains("Microsoft JDBC Driver");
        while (columns.next()) {
            FieldData fieldData = new FieldData();
            String columnName = columns.getString("COLUMN_NAME");
            String typeName = columns.getString("TYPE_NAME");
            String columnSize = columns.getString("COLUMN_SIZE");
            String nullable = columns.getString("NULLABLE");
            String remarks = columns.getString("REMARKS");
            if (isSqlServer) {
                Statement statement = conn.createStatement();
                ResultSet resultSet = statement.executeQuery("select t1.name as tname,t2.name as cname, t3.value from sys.tables t1 inner join sys.columns t2 on t1.object_id=t2.object_id" +
                        " left join sys.extended_properties t3 on t2.object_id=t3.major_id and t2.column_id=t3.minor_id" +
                        " where t1.name='" + tableName + "' and t2.name='" + columnName + "'");
                while (resultSet.next()) {
                    remarks = resultSet.getString("value");
                    if (remarks == null) {
                        remarks = "";
                    }
                }
            }

            String fieldName = DbColumnUtil.getFieldByColumn(columnName);
            String fieldType = DbToJavaParamTypeMap.getJavaParamType(typeName);
            fieldData.setColumnName(columnName);
            fieldData.setColumnType(typeName);
            fieldData.setColumnSize(columnSize);
            fieldData.setColumnRemarks(remarks);
            fieldData.setIsNullAble(nullable);
            fieldData.setFieldName(fieldName);
            fieldData.setFieldType(fieldType);
            fieldData.setFieldRemarks(remarks);
            fieldData.setIsPrimaryKey(primaryKeyList.contains(columnName));
            list.add(fieldData);
        }
        return list;
    }

    /**
     * 获取数据库主键字段列表
     *
     * @param tableName
     * @return
     */
    public static List<String> getPrimaryKeyList(String tableName) throws Exception {
        ResultSet rs = conn.getMetaData().getPrimaryKeys(conn.getCatalog(), null, tableName);
        List<String> primaryKeyList = new ArrayList<>();
        while (rs.next()) {
            String primaryKeyName = rs.getString("COLUMN_NAME");
            primaryKeyList.add(primaryKeyName);
        }
        return primaryKeyList;
    }
}
